"""
教师管理视图函数
@file admin/teacher_views.py
@description 处理教师课程和班级学生成绩相关的视图函数
@created 2024-06-27
"""
from django.http import JsonResponse
from django.db import connection
import json
import logging
import random
from datetime import datetime, timedelta

# 设置日志
logger = logging.getLogger(__name__)

def test_connection(request):
    """
    测试API连接
    @param request: HTTP请求对象
    @return: 测试成功响应
    """
    try:
        return JsonResponse({
            'status': 'success',
            'message': 'API连接测试成功',
            'time': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        })
    except Exception as e:
        logger.error(f"API连接测试失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'API连接测试失败: {str(e)}',
        }, status=500)

def teacher_courses(request, teacher_id):
    """
    获取教师授课的课程和班级信息 (使用 yzh_ 前缀的表)
    @param request: HTTP请求对象
    @param teacher_id: 教师ID
    @return: 教师课程和班级信息
    """
    try:
        # 检查是否已经有课程数据，如果没有则生成测试数据
        # ensure_course_data_exists() # 暂时注释掉，操作旧表
        
        # 检查该教师是否已有课程分配，如果没有则为其分配课程
        # ensure_teacher_has_courses(teacher_id) # 暂时注释掉，操作旧表
        
        courses_data = {} # 用于存储课程及其班级信息，以 course_id 为键

        with connection.cursor() as cursor:
            # 查询教师的教学安排、课程信息和班级信息
            sql_query = """
                SELECT 
                    ta.course_id, 
                    ta.class_id,
                    ta.semester,
                    ta.year,
                    c.id AS course_db_id,      -- yzh_courses 表的 id
                    c.course_code,
                    c.name AS course_name,     -- yzh_courses 表的 name
                    c.credit,
                    c.hours,
                    cl.id AS class_db_id,      -- yzh_classes 表的 id
                    cl.name AS class_name,     -- yzh_classes 表的 name
                    cl.grade_year,
                    cl.department_id,
                    cl.college_id
                FROM yzh_teaching_assignments ta
                JOIN yzh_courses c ON ta.course_id = c.id
                JOIN yzh_classes cl ON ta.class_id = cl.id
                WHERE ta.teacher_id = %s AND ta.status = 1 
                ORDER BY c.id, cl.id; 
            """
            cursor.execute(sql_query, [teacher_id])
            
            assignment_results = cursor.fetchall()
            
            # 获取列名，以便将元组转换为字典
            columns = [col[0] for col in cursor.description]

            for row_tuple in assignment_results:
                row = dict(zip(columns, row_tuple))
                
                course_id = row['course_id'] # 这是 yzh_courses.id
                
                if course_id not in courses_data:
                    courses_data[course_id] = {
                        'id': course_id, # 使用 yzh_courses.id 作为课程的唯一标识符
                        'course_code': row['course_code'],
                        'course_name': row['course_name'],
                        'credit': row['credit'],
                        'hours': row['hours'],
                        'classes': []
                    }
                
                # 添加班级信息到对应课程的 classes 列表
                # 前端期望的 classItem 包含 id, class_name, student_count, semester, year
                # student_count 需要额外查询或估算，这里暂时留空或设为0
                courses_data[course_id]['classes'].append({
                    'id': row['class_id'], # 使用 yzh_classes.id 作为班级的唯一标识符
                    'class_name': row['class_name'],
                    'semester': row['semester'],
                    'year': row['year'],
                    'student_count': 0, # TODO: 实现学生数量的查询
                    # 可以根据需要添加其他班级相关字段，如 grade_year, department_id, college_id
                    'grade_year': row['grade_year'] 
                })
            
            # 将字典的值转换为列表，作为最终返回的课程列表
            courses_list = list(courses_data.values())
            
            return JsonResponse({
                'status': 'success',
                'message': '获取教师课程成功',
                'data': {
                    'courses': courses_list
                }
            })
            
    except Exception as e:
        logger.error(f"获取教师课程失败 (yzh): {str(e)}") # 添加 (yzh) 标识
        return JsonResponse({
            'status': 'error',
            'message': f'获取教师课程失败: {str(e)}',
            'data': None
        }, status=500)

def class_students_scores(request, teacher_id, course_id, class_id):
    """
    获取班级学生成绩信息 (使用 yzh_ 前缀的表)
    @param request: HTTP请求对象
    @param teacher_id: 教师ID (来自 yzh_teachers.id)
    @param course_id: 课程ID (来自 yzh_courses.id)
    @param class_id: 班级ID (来自 yzh_classes.id)
    @return: 班级学生成绩信息
    """
    try:
        # ensure_course_data_exists() # 暂时注释掉旧的测试数据生成
        
        teaching_assignment_id = None
        with connection.cursor() as cursor:
            # 1. 验证教学安排是否存在，并获取 teaching_assignment_id
            cursor.execute("""
                SELECT id 
                FROM yzh_teaching_assignments
                WHERE teacher_id = %s AND course_id = %s AND class_id = %s AND status = 1
            """, [teacher_id, course_id, class_id])
            assignment_row = cursor.fetchone()
            
            if not assignment_row:
                return JsonResponse({
                    'status': 'error',
                    'message': '指定的教学安排不存在或无效。',
                    'data': None
                }, status=404) # 使用404更合适，因为资源未找到
            teaching_assignment_id = assignment_row[0]

            # 2. 获取班级、课程、教师基本信息
            # 班级名称
            cursor.execute("SELECT name FROM yzh_classes WHERE id = %s", [class_id])
            class_name_row = cursor.fetchone()
            class_name = class_name_row[0] if class_name_row else '未知班级'
            
            # 课程名称
            cursor.execute("SELECT name FROM yzh_courses WHERE id = %s", [course_id])
            course_name_row = cursor.fetchone()
            course_name = course_name_row[0] if course_name_row else '未知课程'
            
            # 教师名称
            cursor.execute("SELECT name FROM yzh_teachers WHERE id = %s", [teacher_id])
            teacher_name_row = cursor.fetchone()
            teacher_name = teacher_name_row[0] if teacher_name_row else '未知教师'

            # 3. 获取班级内的学生列表
            cursor.execute("""
                SELECT id, student_code, name 
                FROM yzh_students 
                WHERE class_id = %s AND status = 1
            """, [class_id])
            students_in_class = cursor.fetchall()
            student_columns = [col[0] for col in cursor.description]
            
            students_data = []
            for student_tuple in students_in_class:
                student_info = dict(zip(student_columns, student_tuple))
                current_student_id = student_info['id']
                
                # 4. 获取当前学生的成绩
                # 优先使用 teaching_assignment_id 查询成绩，如果 yzh_grades 表确实以此为主键关联
                # 否则，可以考虑用 student_id 和 course_id 查询
                cursor.execute("""
                    SELECT score 
                    FROM yzh_grades 
                    WHERE student_id = %s AND teaching_assignment_id = %s
                """, [current_student_id, teaching_assignment_id])
                # 如果 yzh_grades.teaching_assignment_id 不可靠或不存在，备选方案:
                # cursor.execute("""
                #     SELECT score 
                #     FROM yzh_grades 
                #     WHERE student_id = %s AND course_id = %s
                # """, [current_student_id, course_id])
                grade_row = cursor.fetchone()
                score = grade_row[0] if grade_row and grade_row[0] is not None else None
                
                students_data.append({
                    'student_id': current_student_id,
                    'student_code': student_info['student_code'],
                    'name': student_info['name'],
                    'score': float(score) if score is not None else None, # 确保score是浮点数或null
                    # TODO: 根据需要添加其他信息，如出勤率、作业率等，这些需要额外查询
                    'attendance_rate': None, 
                    'homework_rate': None
                })

            # 统计信息 (可选，如果需要)
            # average_score, pass_rate, max_score 等可以基于 students_data 中的 score 计算，或单独SQL查询
            valid_scores = [s['score'] for s in students_data if s['score'] is not None]
            average_score = sum(valid_scores) / len(valid_scores) if valid_scores else 0
            pass_rate = (sum(1 for s in valid_scores if s >= 60) / len(valid_scores) * 100) if valid_scores else 0
            max_score = max(valid_scores) if valid_scores else 0
            student_count = len(students_data)
            
            return JsonResponse({
                'status': 'success',
                'message': '获取班级学生成绩成功',
                'data': {
                    'classInfo': {
                        'class_id': class_id,
                        'class_name': class_name,
                        'course_id': course_id,
                        'course_name': course_name,
                        'teacher_id': teacher_id,
                        'teacher_name': teacher_name,
                        'student_count': student_count
                    },
                    'stats': {
                    'average_score': round(average_score, 1),
                    'pass_rate': round(pass_rate, 1),
                    'max_score': round(max_score, 1) if max_score else None
                    },
                    'students': students_data
                }
            })
            
    except Exception as e:
        logger.error(f"获取班级学生成绩失败 (yzh): {str(e)}") # 添加(yzh) 标识
        return JsonResponse({
            'status': 'error',
            'message': f'获取班级学生成绩失败: {str(e)}',
            'data': None
        }, status=500)


# ----- 生成测试数据相关函数 -----

def ensure_course_data_exists():
    """
    确保课程相关的数据存在，如果不存在则生成测试数据
    """
    with connection.cursor() as cursor:
        # 首先确保学院表中有数据
        cursor.execute("SELECT COUNT(*) FROM colleges")
        college_count = cursor.fetchone()[0]
        
        if college_count == 0:
            logger.info("学院表为空，添加基础学院数据...")
            colleges = [
                (1, "计算机科学与技术学院"),
                (2, "法学院"),
                (3, "经济与金融学院"),
                (4, "建筑工程学院"),
                (5, "管理学院")
            ]
            
            for college_id, name in colleges:
                try:
                    cursor.execute(
                        "INSERT INTO colleges (id, name) VALUES (%s, %s)",
                        [college_id, name]
                    )
                    logger.info(f"添加学院: {name}")
                except Exception as e:
                    logger.error(f"添加学院 {name} 失败: {str(e)}")
        
        # 检查courses表中是否有数据
        cursor.execute("SELECT COUNT(*) FROM courses")
        course_count = cursor.fetchone()[0]
        
        if course_count == 0:
            logger.info("未发现课程数据，开始生成测试数据...")
            generate_course_data()
        else:
            logger.info(f"已存在 {course_count} 条课程数据")


def ensure_teacher_has_courses(teacher_id):
    """
    确保指定教师有课程数据，如果没有则为其分配课程
    @param teacher_id: 教师ID
    """
    with connection.cursor() as cursor:
        # 检查该教师是否有教学记录
        cursor.execute("SELECT COUNT(*) FROM teaching_record WHERE teacher_id = %s", [teacher_id])
        record_count = cursor.fetchone()[0]
        
        if record_count == 0:
            logger.info(f"教师 {teacher_id} 没有课程数据，为其分配课程...")
            assign_courses_to_teacher(teacher_id)
        else:
            logger.info(f"教师 {teacher_id} 已有 {record_count} 条教学记录")


def generate_course_data():
    """
    生成基础测试数据，包括班级和学生
    """
    try:
        with connection.cursor() as cursor:
            # 确保学院表中有数据
            cursor.execute("SELECT COUNT(*) FROM colleges")
            college_count = cursor.fetchone()[0]
            
            if college_count == 0:
                logger.error("学院表为空，无法创建班级")
                return
                
            # 获取所有学院
            cursor.execute("SELECT id, name FROM colleges")
            colleges = cursor.fetchall()
            
            # 生成班级数据
            class_ids = []
            for college_id, college_name in colleges:
                # 每个学院创建多个班级
                # 不同年级
                for grade_year in [2020, 2021, 2022, 2023]:
                    # 不同专业
                    specialties = []
                    if college_id == 1:  # 计算机学院
                        specialties = ["计算机科学与技术", "软件工程", "人工智能", "网络工程"]
                    elif college_id == 2:  # 法学院
                        specialties = ["法学", "国际法", "经济法", "民商法"]
                    elif college_id == 3:  # 经济学院
                        specialties = ["经济学", "金融学", "国际经济与贸易", "财政学"]
                    elif college_id == 4:  # 工程学院
                        specialties = ["土木工程", "建筑学", "机械工程", "电气工程"]
                    elif college_id == 5:  # 管理学院
                        specialties = ["工商管理", "市场营销", "会计学", "人力资源管理"]
                    else:
                        specialties = ["通用专业"]
                        
                    for specialty in specialties:
                        # 每个专业1-2个班
                        for class_num in range(1, random.randint(2, 3)):
                            grade = f"{grade_year}级"
                            class_name = f"{college_name}{grade}{specialty}{class_num}班"
                            department = f"{specialty}系"
                            
                            try:
                                cursor.execute("""
                                    INSERT INTO school_classes (name, grade_id, college_id, department, status)
                                    VALUES (%s, %s, %s, %s, 1)
                                """, [
                                    class_name,
                                    grade_year,  # 使用年份作为grade_id
                                    college_id,
                                    department
                                ])
                                
                                # 获取最后插入的ID
                                cursor.execute("SELECT LAST_INSERT_ID()")
                                class_id = cursor.fetchone()[0]
                                class_ids.append(class_id)
                                logger.info(f"创建班级: {class_name} (ID: {class_id})")
                                
                                # 为每个班级生成20-40名学生
                                student_count = random.randint(20, 40)
                                for j in range(1, student_count + 1):
                                    student_number = f"{grade_year}{college_id:02d}{class_id:03d}{j:03d}"
                                    student_name = f"学生{student_number}"
                                    
                                    # 检查学生是否已存在
                                    cursor.execute("SELECT student_id FROM students WHERE name = %s", [student_name])
                                    student = cursor.fetchone()
                                    
                                    if student:
                                        student_id = student[0]
                                    else:
                                        cursor.execute("""
                                            INSERT INTO students (name, email, enrollment_date)
                                            VALUES (%s, %s, %s)
                                        """, [
                                            student_name,
                                            f"student_{student_number}@example.com",
                                            datetime.now() - timedelta(days=random.randint(365, 730))
                                        ])
                                        
                                        # 获取最后插入的ID
                                        cursor.execute("SELECT LAST_INSERT_ID()")
                                        student_id = cursor.fetchone()[0]
                                    
                                    # 将学生与班级关联
                                    try:
                                        cursor.execute("""
                                            INSERT INTO student_class (student_id, class_id)
                                            VALUES (%s, %s)
                                        """, [student_id, class_id])
                                    except Exception as e:
                                        # 可能已存在记录，忽略错误
                                        pass
                            except Exception as e:
                                logger.error(f"创建班级失败: {str(e)}")
            
            # 获取所有教师ID
            cursor.execute("SELECT teacher_id FROM teacher")
            all_teacher_ids = [row[0] for row in cursor.fetchall()]
            
            if not all_teacher_ids:
                logger.warning("未找到教师数据，将不会分配课程")
            else:
                # 为每个教师分配课程
                for teacher_id in all_teacher_ids:
                    assign_courses_to_teacher(teacher_id)
            
            logger.info("班级和学生数据生成完成")
            
    except Exception as e:
        logger.error(f"生成测试数据失败: {str(e)}")


def assign_courses_to_teacher(teacher_id):
    """
    为指定教师分配课程，确保只分配对应学院的课程和班级
    @param teacher_id: 教师ID
    """
    try:
        with connection.cursor() as cursor:
            # 获取教师所属学院
            cursor.execute("SELECT college_id FROM teacher WHERE teacher_id = %s", [teacher_id])
            result = cursor.fetchone()
            
            if not result or not result[0]:
                logger.error(f"未找到教师 {teacher_id} 或教师没有关联的学院")
                
                # 如果教师没有学院，随机分配一个
                college_id = random.randint(1, 5)
                logger.info(f"为教师 {teacher_id} 随机分配学院ID: {college_id}")
                
                # 更新教师的学院ID
                cursor.execute("UPDATE teacher SET college_id = %s WHERE teacher_id = %s", [college_id, teacher_id])
            else:
                college_id = result[0]
            
            # 根据学院选择合适的课程
            course_names_by_college = {
                1: ["数据结构", "计算机组成原理", "操作系统", "计算机网络", "数据库原理", "软件工程", "算法设计与分析", "人工智能"], # 计算机学院
                2: ["法学导论", "宪法学", "民法学", "刑法学"], # 法学院
                3: ["经济学原理", "金融学", "国际经济学", "财务管理", "统计学", "会计学原理"], # 经济学院
                4: ["高等数学", "线性代数", "概率论与数理统计", "物理学", "工程力学"], # 工程学院
                5: ["管理学原理", "市场营销", "人力资源管理", "组织行为学"] # 管理学院
            }
            
            # 获取学院对应的课程名称
            college_course_names = course_names_by_college.get(college_id, [])
            
            # 如果没有对应的课程，使用通用课程
            if not college_course_names:
                college_course_names = ["通识课程", "综合素质培养", "职业发展规划"]
            
            # 随机选择1-3门课程
            num_courses = random.randint(1, 3)
            selected_courses = random.sample(college_course_names, min(num_courses, len(college_course_names)))
            
            # 获取学院名称
            cursor.execute("SELECT name FROM colleges WHERE id = %s", [college_id])
            college_result = cursor.fetchone()
            college_name = college_result[0] if college_result else f"学院{college_id}"
            
            logger.info(f"为学院 '{college_name}' 的教师 {teacher_id} 分配课程")
            
            for course_name in selected_courses:
                # 获取教师姓名
                cursor.execute("SELECT teacher_name FROM teacher WHERE teacher_id = %s", [teacher_id])
                teacher_name_result = cursor.fetchone()
                teacher_name = teacher_name_result[0] if teacher_name_result else f"教师{teacher_id}"
                
                # 创建课程
                cursor.execute("""
                    INSERT INTO courses (name, description, status, teacher_id, teacher_name)
                    VALUES (%s, %s, 1, %s, %s)
                """, [
                    course_name,
                    f"{course_name}是一门重要的专业课程。",
                    teacher_id,
                    teacher_name
                ])
                
                # 获取最后插入的ID
                cursor.execute("SELECT LAST_INSERT_ID()")
                course_id = cursor.fetchone()[0]
                logger.info(f"为教师 {teacher_id} 创建课程: {course_name} (ID: {course_id})")
                
                # 仅选择同学院的班级
                cursor.execute("""
                    SELECT id FROM school_classes 
                    WHERE college_id = %s 
                    LIMIT 20
                """, [college_id])
                
                college_class_ids = [row[0] for row in cursor.fetchall()]
                
                if not college_class_ids:
                    logger.warning(f"学院 {college_id} 没有找到班级，无法分配课程")
                    continue
                
                num_classes = min(random.randint(1, 2), len(college_class_ids))
                selected_class_ids = random.sample(college_class_ids, num_classes)
                
                for class_id in selected_class_ids:
                    # 创建教学记录
                    cursor.execute("""
                        INSERT INTO teaching_record (teacher_id, course_id, class_id, semester_id, start_date, end_date, status)
                        VALUES (%s, %s, %s, %s, %s, %s, 1)
                    """, [
                        teacher_id,
                        course_id,
                        class_id,
                        1,  # 学期ID
                        datetime.now() - timedelta(days=90),  # 开课日期（3个月前）
                        datetime.now() + timedelta(days=90),  # 结课日期（3个月后）
                    ])
                    logger.info(f"创建教学记录: 教师 {teacher_id}, 课程 {course_id}, 班级 {class_id}")
                    
                    # 创建课程安排
                    day_of_week = random.randint(0, 4)  # 周一至周五
                    start_time = random.randint(1, 7)  # 第1-7节
                    end_time = start_time + 1
                    
                    cursor.execute("""
                        INSERT INTO course_schedules (course_id, teacher_id, day_of_week, start_time, end_time, classroom)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """, [
                        course_id,
                        teacher_id,
                        day_of_week,
                        start_time,
                        end_time,
                        f"教学楼{random.randint(1, 5)}-{random.randint(101, 501)}"
                    ])
                    logger.info(f"创建课程安排: 教师 {teacher_id}, 课程 {course_id}, 时间: 周{day_of_week+1} {start_time}-{end_time}节")
                    
                    # 为班级学生生成成绩和出勤记录
                    cursor.execute("SELECT student_id FROM student_class WHERE class_id = %s", [class_id])
                    student_ids = [row[0] for row in cursor.fetchall()]
                    
                    for student_id in student_ids:
                        # 成绩
                        final_grade = round(random.uniform(60, 100), 1)
                        cursor.execute("""
                            INSERT INTO grades (student_id, course_id, final_grade)
                            VALUES (%s, %s, %s)
                        """, [student_id, course_id, final_grade])
                        
                        # 出勤记录（假设有10次课）
                        for i in range(10):
                            status = 1 if random.random() > 0.1 else 0  # 90%出勤率
                            try:
                                cursor.execute("""
                                    INSERT INTO attendance_records (student_id, course_id, class_date, status)
                                    VALUES (%s, %s, %s, %s)
                                """, [
                                    student_id,
                                    course_id,
                                    datetime.now() - timedelta(days=90-i*7),  # 每周一次课
                                    status
                                ])
                            except Exception as e:
                                # 可能已存在记录，忽略错误
                                pass
            
            logger.info(f"成功为教师 {teacher_id} 分配了 {len(selected_courses)} 门课程")
            
    except Exception as e:
        logger.error(f"为教师 {teacher_id} 分配课程失败: {str(e)}") 